--明细
with store_info as ( --门店维表
    select
        store.business_id
         , store.store_code--门店编码
         , store.store_name --门店名称
         , store.store_phone
         , store.store_address
         , store.network_code
         , store.input_type
         , build.build_type
    from
        (
            select
                business_id
                 , courier_station_code  as store_code--门店编码
                 , name as store_name --门店名称
                 , phone as store_phone
                 , detailed_address as  store_address
                 , network_code
                 , input_type
            from  jms_dim.dim_tab_agent_point_base
            where courier_station_code is not null
        )store
            left join
        (
            select  business_id,courier_station_code,build_type,network_code
            from jms_dim.dim_ide_tab_end_brand_info_base
            where status in (1,5,8)--1已认证5已回传
            group by business_id,courier_station_code,build_type,network_code
        ) build on build.courier_station_code=store.store_code
            and build.business_id=store.business_id
            and build.network_code=store.network_code
),
     taking_notout_over22d_detail as (
         select /*+ BROADCAST(s) */
             in_scan.billcode          as waybill_no--运单号 
              , in_scan.order_source_name as ordersource_name --多平台订单来源名称 
              , taking.scan_site_code     as taking_network_code
              , n2.name                   as taking_network_name
              , taking.scan_time          as taking_scan_time
              , in_scan.scantime          as in_scan_time --扫描时间 
              , in_scan.scansitecode      as in_network_code--网点编码 
              , n1.name                   as in_network_name  --最早入库网点
              , in_scan.store_code        as store_code--门店code
              , in_scan.deliver_scan_time              --派件时间
              , in_scan.scan_time_over    as out_scan_time--完结件扫描时间 
              , in_scan.business_id                      --品牌id
              , bus.business_name                        --品牌名称
              , s.store_name                             --门店名称
              , s.store_address                          --门店地址
              , s.build_type                             --建设类型
              , app.app_name              as source_type --末端类型：机柜、驿站
              , n1.agent_code        --代理区code
              , n1.agent_name        --代理区name
              , n1.fran_code         --加盟商code
              , n1.fran_name         --加盟商name
              , datediff(nvl(date(in_scan.scan_time_over ),'{{ execution_date  | cst_ds }}'),date(taking.scan_time))+1 as out_taking_diffday
              , date(in_scan.scantime )           as date_time --查询时间
        , nvl(n1.virt_code,n1.agent_code)   as virt_code --虚拟代理区code
        , nvl(n1.virt_name,n1.agent_name)   as virt_name --虚拟代理区name
from
    (
    select
    billcode --运单号 
        , order_source_name --多平台订单来源名称 
        , scantime --扫描时间 
        , scansitecode --网点编码 
        , store_code --门店code
        , deliver_scan_time --派件时间
        , inputsite --网点名称 
        , business_id --品牌id
        , business_name --品牌name
        , scan_time_over --完结件扫描时间 
    from jms_tmp.tmp_end_network_dispatch_in_detail_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-21) and '{{ execution_date  | cst_ds }}'
    and is_seven_store_over=0
    and date(scantime) between date_add('{{ execution_date  | cst_ds }}',-21) and '{{ execution_date  | cst_ds }}'
    union all
    select
    billcode --运单号 
        , order_source_name --多平台订单来源名称 
        , scantime --扫描时间 
        , scansitecode --网点编码 
        , store_code --门店code
        , deliver_scan_time --派件时间
        , inputsite --网点名称 
        , business_id --品牌id
        , business_name --品牌name
        , nvl(scan_time_out,scan_time_take) as scan_time_over --完结件扫描时间 
    from jms_dm.dm_end_network_dispatch_detail_7not_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-21) and '{{ execution_date  | cst_ds }}'
    and date(scantime) between date_add('{{ execution_date  | cst_ds }}',-21) and '{{ execution_date  | cst_ds }}'
    )in_scan inner join
    (
    select
    waybill_no
        ,scan_site_code
        ,scan_time
        ,row_number() over(partition by waybill_no order by scan_time desc) as rk
    from jms_dwd.dwd_tab_barscan_taking_base_dt
    where dt between date_add('{{ execution_date  | cst_ds }}',-21-30) and '{{ execution_date  | cst_ds }}'
    )taking on in_scan.billcode=taking.waybill_no and taking.rk=1
    left join store_info s on s.store_code=in_scan.store_code and s.network_code=in_scan.scansitecode and in_scan.business_id=s.business_id
    left join jms_dim.dim_network_whole_massage n1 on n1.code=in_scan.scansitecode
    left join jms_dim.dim_network_whole_massage n2 on n2.code=taking.scan_site_code
    left join jms_dim.dim_tab_terminal_secret_base bus on bus.business_id=in_scan.business_id
    left join jms_dim.dim_tab_terminal_application_base app on app.app_id=bus.app_id --获取末端类型
    )

insert overwrite table jms_dm.dm_end_network_taking_notout_over22d_detail_dt partition(dt,is_not_out)
select waybill_no                                 --运单号
     , ordersource_name                           --订单来源
     , taking_network_code                        --最早揽收网点
     , taking_network_name
     , taking_scan_time                           --最早揽收时间
     , in_scan_time                               --最早入库时间
     , in_network_code                            --最早入库网点
     , in_network_name                            --最早入库网点
     , store_code                                 --最早入库门店
     , deliver_scan_time                          --最早派件时间
     , out_scan_time                              --出库扫描时间
     , business_id                                --品牌id
     , business_name                              --品牌名称
     , store_name                                 --门店名称
     , store_address                              --门店地址
     , build_type                                 --建设类型
     , source_type                                --末端类型：、机柜、驿站
     , agent_code                                 --代理区code
     , agent_name                                 --代理区name
     , fran_code                                  --加盟商code
     , fran_name                                  --加盟商name
     , out_taking_diffday
     , date (in_scan_time) as date_time           --查询时间
     , virt_code                                  --虚拟代理区code
     , virt_name                                  --虚拟代理区name
     , 1 as detail_type
     , date (in_scan_time) as dt                   --时间分区
     , if(out_taking_diffday>=21, 1, 0) as is_not_out
from taking_notout_over22d_detail

union all

select waybill_no                                  --运单号
     , ordersource_name                            --订单来源
     , taking_network_code                         --最早揽收网点
     , taking_network_name
     , taking_scan_time                            --最早揽收时间
     , in_scan_time                                --最早入库时间
     , in_network_code                             --最早入库网点
     , in_network_name                             --最早入库网点
     , store_code                                  --最早入库门店
     , deliver_scan_time                           --最早派件时间
     , out_scan_time                               --出库扫描时间
     , business_id                                 --品牌id
     , business_name                               --品牌名称
     , store_name                                  --门店名称
     , store_address                               --门店地址
     , build_type                                  --建设类型
     , source_type                                 --末端类型：、机柜、驿站
     , agent_code                                  --代理区code
     , agent_name                                  --代理区name
     , fran_code                                   --加盟商code
     , fran_name                                   --加盟商name
     , out_taking_diffday
     , date (taking_scan_time) as date_time        --查询时间
     , virt_code                                   --虚拟代理区code
     , virt_name                                   --虚拟代理区name
     , 2 as detail_type
     , date (taking_scan_time) as dt                   --时间分区
     , if(out_taking_diffday>=21, 1, 0) as is_not_out
from taking_notout_over22d_detail
where  date(taking_scan_time) between date_add('{{ execution_date  | cst_ds }}',-21) and '{{ execution_date  | cst_ds }}'
    distribute by dt,is_not_out,pmod(hash(rand()), 40)
;
---------------------------------------------------------------------------------------------------------------
-----汇总
---派件量取值
with tab_deliver as (
    select
        ordersource_name    --订单来源
         , in_network_code    --最早入库网点
         , in_network_name    --最早入库网点
         , agent_code         --代理区code
         , agent_name         --代理区name
         , fran_code          --加盟商code
         , fran_name          --加盟商name
         , dt
         , virt_code          --虚拟代理区code
         , virt_name          --虚拟代理区name
         , network_dispatch_total
         , tmp_type as cnt_type
         , dt as date_time
    from
        (
            select
                order_source_name as ordersource_name    --订单来源
                 , scansitecode as in_network_code    --最早入库网点
                 , inputsite as in_network_name    --最早入库网点
                 , agent_code         --代理区code
                 , agent_name         --代理区name
                 , franchisee_code as fran_code          --加盟商code
                 , franchisee_name as fran_name          --加盟商name
                 , dt
                 , virt_code          --虚拟代理区code
                 , virt_name          --虚拟代理区name
                 , network_dispatch_total
            from jms_dm.dm_network_end_dispatch_diff_rate
            where dt between date_add('{{ execution_date  | cst_ds }}',-21) and '{{ execution_date  | cst_ds }}'
            group by
                order_source_name    --订单来源
                   , scansitecode    --最早入库网点
                   , inputsite    --最早入库网点
                   , agent_code         --代理区code
                   , agent_name         --代理区name
                   , franchisee_code          --加盟商code
                   , franchisee_name          --加盟商name
                   , dt
                   , virt_code          --虚拟代理区code
                   , virt_name          --虚拟代理区name
                   , network_dispatch_total
        )t LATERAL VIEW explode(array(1,2)) tmp as tmp_type
    )
insert overwrite table jms_dm.dm_end_network_taking_notout_over22d_cnt_dt partition(dt)
select
    nvl(main.ordersource_name , deliver.ordersource_name ) as  ordersource_name  --订单来源
     , nvl(main.in_network_code  , deliver.in_network_code  ) as  in_network_code   --最早入库网点
     , nvl(main.in_network_name  , deliver.in_network_name  ) as  in_network_name   --最早入库网点
     , nvl(main.store_code       , null       ) as  store_code        --最早入库门店
     , nvl(main.business_id      , null       ) as  business_id       --品牌id
     , nvl(main.business_name    , null       ) as  business_name     --品牌名称
     , nvl(main.store_name       , null       ) as  store_name        --门店名称
     , nvl(main.store_address    , null       ) as  store_address     --门店地址
     , nvl(main.build_type       , null       ) as  build_type        --建设类型
     , nvl(main.source_type      , null       ) as  source_type       --末端类型：机柜、驿站
     , nvl(main.agent_code       , deliver.agent_code       ) as  agent_code        --代理区code
     , nvl(main.agent_name       , deliver.agent_name       ) as  agent_name        --代理区name
     , nvl(main.fran_code        , deliver.fran_code        ) as  fran_code         --加盟商code
     , nvl(main.fran_name        , deliver.fran_name        ) as  fran_name         --加盟商name
     , nvl(deliver.network_dispatch_total, 0                ) as  in_cnt            --入库量    
     , nvl(main.in_cnt           , 0                        ) as  in_cnt            --入库量
     , nvl(main.notout_21d_cnt   , 0                        ) as  notout_21d_cnt    --揽收超21日出库量
     , nvl(main.notout_22d_cnt   , 0                        ) as  notout_22d_cnt    --揽收超22日出库量
     , nvl(main.date_time        , deliver.date_time        ) as  date_time         --查询时间
     , nvl(main.cnt_type         , deliver.cnt_type         ) as  cnt_type          --入库维度统计
     , nvl(main.virt_code        , deliver.virt_code        ) as  virt_code         --虚拟代理区code
     , nvl(main.virt_name        , deliver.virt_name        ) as  virt_name         --虚拟代理区name
     , nvl(main.dt               , deliver.dt               ) as  dt                --时间分区
from
    (
        --入库维度
        select
            ordersource_name   --订单来源
             , in_network_code    --最早入库网点
             , in_network_name    --最早入库网点
             , store_code         --最早入库门店
             , business_id        --品牌id
             , business_name      --品牌名称
             , store_name         --门店名称
             , store_address      --门店地址
             , build_type         --建设类型
             , source_type        --末端类型：机柜、驿站
             , agent_code        --代理区code
             , agent_name        --代理区name
             , fran_code         --加盟商code
             , fran_name         --加盟商name
        --, count(deliver_scan_time) as deliver_cnt --派件量
             , count(1) as in_cnt --入库量
             , count(
                if(out_taking_diffday>=21,waybill_no,null )) as notout_21d_cnt --揽收超21日出库量
             , count(
                if(out_taking_diffday>=22,waybill_no,null )) as notout_22d_cnt --揽收超22日出库量
             , date_time --查询时间
             , detail_type as cnt_type --入库维度统计
             , virt_code       --虚拟代理区code
             , virt_name         --虚拟代理区name
             , date(dt) as dt --时间分区
    from jms_dm.dm_end_network_taking_notout_over22d_detail_dt
where dt between date_add('{{ execution_date  | cst_ds }}',-21) and '{{ execution_date  | cst_ds }}'
group by
    ordersource_name   --订单来源
        , in_network_code    --最早入库网点
        , in_network_name  --最早入库网点
        , store_code         --最早入库门店
        , business_id        --品牌id
        , business_name      --品牌名称
        , store_name         --门店名称
        , store_address      --门店地址
        , build_type         --建设类型
        , source_type      --末端类型：机柜、驿站
        , agent_code        --代理区code
        , agent_name        --代理区name
        , fran_code         --加盟商code
        , fran_name         --加盟商name
        , date_time         --查询时间
        , dt                --时间分区
        , virt_code         --虚拟代理区code
        , virt_name         --虚拟代理区name
        , detail_type
    )main
    full join
    tab_deliver deliver
on main.ordersource_name = deliver.ordersource_name
    and main.in_network_code = deliver.in_network_code
    and main.dt = deliver.dt
    and main.cnt_type=deliver.cnt_type
    distribute by dt;


